using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

using GonzalesCore.CoreElements;

using Kypris2.Utils;
using Kypris2.CoreElements;
using Kypris2.CoreManagers;

using Jamila2.CoreElements;
using Jamila2.Database;
using Jamila2.Tools;

namespace GonzalesCore.CoreManagers
{
    public class FileManager : KyprisManagerBase, IManager, IDisposable
    {
        private GonzalesDataStructure DataStructrure = new GonzalesDataStructure();
        private PersonalSessionAccount CurrentModuleSession = null;

        public FileManager(PersonalSessionAccount PrivateConfig)
            : base(PrivateConfig)
        {
            this.CurrentModuleSession = PrivateConfig;
        }

        #region GetFilesByClaimID

        public SupportingDocuments GetFilesByClaimID(string ClaimID)
        {
            SupportingDocuments ResultCollection = null;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Views.MasterFiles.ClaimID.ActualFieldName, ClaimID));

            if (this.TryConnection())
            {
                this.CurSQLFactory.SelectCommand(KeyParameters, this.DataStructrure.Views.MasterFiles.ActualTableName);
                DataTable ResultTable = this.CurDBEngine.SelectQuery(this.CurSQLFactory.SQL);

                if (ResultTable != null)
                {
                    ResultCollection = new SupportingDocuments();

                    foreach (DataRow ResultRow in ResultTable.Rows)
                    {
                        SupportingDocument NewDoc = new SupportingDocument(
                            ResultRow[this.DataStructrure.Views.MasterFiles.InternalID.ActualFieldName].ToString(),
                            ResultRow[this.DataStructrure.Views.MasterFiles.FileName.ActualFieldName].ToString());

                        NewDoc.FileType = ResultRow[this.DataStructrure.Views.MasterFiles.FileType.ActualFieldName].ToString();
                        NewDoc.UploadDate = DateTime.Parse(ResultRow[this.DataStructrure.Views.MasterFiles.UploadDate.ActualFieldName].ToString());

                        NewDoc.UploadedBy = new SystemUser(
                            ResultRow[this.DataStructrure.Views.MasterFiles.UploadedBy.ActualFieldName].ToString(),
                            ResultRow[this.DataStructrure.Views.MasterFiles.EmployeeDisplayName.ActualFieldName].ToString());

                        NewDoc.UploadedBy.Email = ResultRow[this.DataStructrure.Views.MasterFiles.Email.ActualFieldName].ToString();
                        NewDoc.UploadedBy.EmailID = ResultRow[this.DataStructrure.Views.MasterFiles.EmailID.ActualFieldName].ToString();

                        ResultCollection.Add(NewDoc);
                    }
                }
                else
                {
                    this.ErrMsg = "[FileManager.GetFilesByClaimID] : Failed at this.CurDBEngine.SelectQuery('" + this.CurSQLFactory.SQL + "') : " + this.ErrMsg;
                }
            }
            else
            {
                this.ErrMsg = "[FileManager.GetFilesByClaimID] : " + this.ErrMsg;
            }

            return ResultCollection;
        }

        #endregion

        #region DeleteFile

        public bool DeleteFile(string FileID)
        {
            bool smooth = true;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.MasterFile.InternalID.ActualFieldName, FileID));

            if (this.TryConnection())
            {
                this.CurSQLFactory.DeleteCommand(KeyParameters, this.DataStructrure.Tables.MasterFile.ActualTableName);
                if (!(smooth = this.CurDBEngine.ExecuteQuery(this.CurSQLFactory.SQL)))
                {
                    this.ErrMsg = "[FileManager.DeleteFile] : Failed at this.CurDBEngine.ExecuteQuery('" + this.CurSQLFactory.SQL + "') : " + this.CurDBEngine.ErrorMessage;
                }
            }
            else
            {
                this.ErrMsg = "[FileManager.DeleteFile] : " + this.ErrMsg;
            }

            return smooth;
        }

        #endregion

        #region UploadNewFile
        public bool UploadNewFile(SupportingDocument NewDoc, string ClaimID, string UploadedBy)
        {
            bool smooth = true;

            // database operation here handle differently
            SqlCommand UploadComand = new SqlCommand(this.DataStructrure.StoredProcedures.UploadNewFile.ActualTableName);
            UploadComand.CommandType = CommandType.StoredProcedure;            

            UploadComand.Parameters.Add(this.DataStructrure.StoredProcedures.UploadNewFile.Param_ClaimID.ActualFieldName, SqlDbType.VarChar);
            UploadComand.Parameters.Add(this.DataStructrure.StoredProcedures.UploadNewFile.Param_FileName.ActualFieldName, SqlDbType.NVarChar);
            UploadComand.Parameters.Add(this.DataStructrure.StoredProcedures.UploadNewFile.Param_FileContent.ActualFieldName, SqlDbType.Binary);
            UploadComand.Parameters.Add(this.DataStructrure.StoredProcedures.UploadNewFile.Param_FileType.ActualFieldName, SqlDbType.VarChar);
            UploadComand.Parameters.Add(this.DataStructrure.StoredProcedures.UploadNewFile.Param_UploadedBy.ActualFieldName, SqlDbType.VarChar);            

            UploadComand.Parameters[0].Value = ClaimID;
            UploadComand.Parameters[1].Value = NewDoc.FileName;
            UploadComand.Parameters[2].Value = NewDoc.BufferContent;
            UploadComand.Parameters[3].Value = NewDoc.FileType;
            UploadComand.Parameters[4].Value = UploadedBy;  

            using (SqlConnection CurConnection = new SqlConnection(this.CurrentModuleSession.DatabaseConnectionString))
            {
                try
                {
                    UploadComand.Connection = CurConnection;
                    CurConnection.Open();

                    smooth = (UploadComand.ExecuteNonQuery() > 0);
                }
                catch (SqlException ex)
                {
                    smooth = false;
                    this.ErrMsg = "[FileManager.UploadNewFile] : Exception : " + ex.Message;
                }
                finally
                {
                    CurConnection.Close();                    
                }
            }

            UploadComand.Dispose();
            UploadComand = null;

            return smooth;
        }
        #endregion

        #region GetFileByID

        public SupportingDocument GetFileByID(string FileID)
        {
            SupportingDocument ResultDocument = null;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Views.MasterFiles.InternalID.ActualFieldName, FileID));

            if (this.TryConnection())
            {
                this.CurSQLFactory.SelectCommand(KeyParameters, this.DataStructrure.Tables.MasterFile.ActualTableName);
                DataTable ResultTable = this.CurDBEngine.SelectQuery(this.CurSQLFactory.SQL);

                if (ResultTable != null && ResultTable.Rows.Count == 1)
                {
                    try
                    {
                        ResultDocument = new SupportingDocument(
                            ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.InternalID.ActualFieldName].ToString(),
                            ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.FileName.ActualFieldName].ToString());

                        ResultDocument.BufferContent = (byte[])ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.FileContent.ActualFieldName];
                        ResultDocument.FileType = ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.FileType.ActualFieldName].ToString();
                        ResultDocument.UploadDate = DateTime.Parse(ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.UploadDate.ActualFieldName].ToString());

                        ResultDocument.UploadedBy = new SystemUser(
                            ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.UploadedBy.ActualFieldName].ToString(),
                            ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.EmployeeDisplayName.ActualFieldName].ToString());

                        ResultDocument.UploadedBy.Email = ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.Email.ActualFieldName].ToString();
                        ResultDocument.UploadedBy.EmailID = ResultTable.Rows[0][this.DataStructrure.Views.MasterFiles.EmailID.ActualFieldName].ToString();

                    }
                    catch (Exception ex)
                    {
                        this.ErrMsg = "[FileManager.GetFileByID] : Exception : " + ex.Message;
                    }
                }
                else
                {
                    this.ErrMsg = "[FileManager.GetFileByID] : Failed at this.CurDBEngine.SelectQuery('" + this.CurSQLFactory.SQL + "') : " + this.CurDBEngine.ErrorMessage;
                }
            }
            else
            {
                this.ErrMsg = "[FileManager.GetFileByID] : " + this.ErrMsg;
            }

            return ResultDocument;

        }

        #endregion
    }
}
